Excel worksheet is made up of thousands of rows and columns. It is very easy to delete rows and columns. You only need to use the “clear” code. Compared with deleting and hiding rows and columns, adding rows and columns is a little complex. Now, let us learn how to add excel rows in worksheet with C#,VB.NET.
Please follow the below procedure to realize the tasks.
Procedure:
Please follow the below procedure to realize the tasks.
Procedure:
Step 1. Install Spire.XLS or Spire.Office and Visual Studio in the system.
Spire.Office for .NET is a compilation of every .NET component offered by e-iceblue. It includes Spire.Doc, Spire.PDFViewer, Spire.XLS, Spire.PDF and Spire.DataExport. Spire.Office contains the most up-to-date versions of the components above.
Freely Download Spire.XLS
Spire.Office for .NET is a compilation of every .NET component offered by e-iceblue. It includes Spire.Doc, Spire.PDFViewer, Spire.XLS, Spire.PDF and Spire.DataExport. Spire.Office contains the most up-to-date versions of the components above.
Freely Download Spire.XLS
Step2. Create a new project in Visual Studio.
Please choose .NET Framework 4 AS THE Target framework.
Step3. Add excel row in worksheet
1.Add Spire.XLS Dll and System.Data as references.
2.Load an excel file from system.
C# Code:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Program Files\e-iceblue\Spire.XLS\Demos\Data\parts.xls");
Worksheet sheet = workbook.Worksheets[0];
VB.NET Code:
Dim workbook As New Workbook()
workbook.LoadFromFile(@"C:\Program Files\e-iceblue\Spire.XLS\Demos\Data\parts.xls"")
Dim sheet As Worksheet = workbook.Worksheets(0)
Please choose .NET Framework 4 AS THE Target framework.
Step3. Add excel row in worksheet
1.Add Spire.XLS Dll and System.Data as references.
2.Load an excel file from system.
C# Code:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Program Files\e-iceblue\Spire.XLS\Demos\Data\parts.xls");
Worksheet sheet = workbook.Worksheets[0];
VB.NET Code:
Dim workbook As New Workbook()
workbook.LoadFromFile(@"C:\Program Files\e-iceblue\Spire.XLS\Demos\Data\parts.xls"")
Dim sheet As Worksheet = workbook.Worksheets(0)
3.Add rows in sheet and set their style.
C# Code:
//add 3 rows in sheet
sheet.InsertRow(3, 3);
//set the new rows' style
sheet.Range["A3:G5"].Style.Color = Color.GreenYellow;
sheet.Range["A3:G5"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A3:B5"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C3:C5"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D3:G5"].HorizontalAlignment = HorizontalAlignType.Right;
//add one row in sheet
sheet.InsertRow(7);
//set the style of the added row
sheet.Range["A7:G7"].Style.Color = Color.GreenYellow;
sheet.Range["A7:G7"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A7:B7"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C7"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D7:G7"].HorizontalAlignment = HorizontalAlignType.Right;
C# Code:
//add 3 rows in sheet
sheet.InsertRow(3, 3);
//set the new rows' style
sheet.Range["A3:G5"].Style.Color = Color.GreenYellow;
sheet.Range["A3:G5"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A3:B5"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C3:C5"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D3:G5"].HorizontalAlignment = HorizontalAlignType.Right;
//add one row in sheet
sheet.InsertRow(7);
//set the style of the added row
sheet.Range["A7:G7"].Style.Color = Color.GreenYellow;
sheet.Range["A7:G7"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A7:B7"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C7"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D7:G7"].HorizontalAlignment = HorizontalAlignType.Right;
VB.NET Code:
'add 3 rows in sheet
sheet.InsertRow(3,3);
'set the new rows' style
sheet.Range("A3:G5").Style.Color = Color.GreenYellow;
sheet.Range("A3:G5").Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range("A3:G5").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.Non;
sheet.Range("A3:G5").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None;
sheet.Range("A3:B5").HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range("C3:C5").HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range("D3:G5").HorizontalAlignment = HorizontalAlignType.Right;
'add one row in sheet
sheet.InsertRow(7);
'set the style of the added row
sheet.Range("A7:G7").Style.Color = Color.GreenYellow;
sheet.Range("A7:G7").Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range("A7:G7").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None;
sheet.Range("A7:G7").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None;
sheet.Range("A7:B7").HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range("C7").HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range("D7:G7").HorizontalAlignment = HorizontalAlignType.Right;
'add 3 rows in sheet
sheet.InsertRow(3,3);
'set the new rows' style
sheet.Range("A3:G5").Style.Color = Color.GreenYellow;
sheet.Range("A3:G5").Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range("A3:G5").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.Non;
sheet.Range("A3:G5").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None;
sheet.Range("A3:B5").HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range("C3:C5").HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range("D3:G5").HorizontalAlignment = HorizontalAlignType.Right;
'add one row in sheet
sheet.InsertRow(7);
'set the style of the added row
sheet.Range("A7:G7").Style.Color = Color.GreenYellow;
sheet.Range("A7:G7").Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range("A7:G7").Borders(BordersLineType.DiagonalDown).LineStyle = LineStyleType.None;
sheet.Range("A7:G7").Borders(BordersLineType.DiagonalUp).LineStyle = LineStyleType.None;
sheet.Range("A7:B7").HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range("C7").HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range("D7:G7").HorizontalAlignment = HorizontalAlignType.Right;
4.Insert data into the new rows.
C# Code:
//insert data into the new rows
DataTable parts = GetData();
for (int i = 3, datarowIndex = 8; i <= 5; datarowIndex++, i++)
{
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[i, j].Value2 = parts.Rows[datarowIndex][j - 1];
}
}
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[7, j].Value2 = parts.Rows[6][j - 1];
}
C# Code:
//insert data into the new rows
DataTable parts = GetData();
for (int i = 3, datarowIndex = 8; i <= 5; datarowIndex++, i++)
{
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[i, j].Value2 = parts.Rows[datarowIndex][j - 1];
}
}
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[7, j].Value2 = parts.Rows[6][j - 1];
}
VB.NET Code:
'insert data into the new rows
Dim parts As DataTable = GetData()
Dim i As Integer = 3
Dim datarowIndex As Integer=8
Do While i <= 5
For j As Integer = 1 To parts.Columns.Count
sheet.Range(i, j).Value2 = parts.Rows(datarowIndex)(j - 1)
Next j
datarowIndex += 1
i += 1
Loop
For j As Integer = 1 To parts.Columns.Count
sheet.Range(7, j).Value2 = parts.Rows(6)(j - 1)
Next j
'insert data into the new rows
Dim parts As DataTable = GetData()
Dim i As Integer = 3
Dim datarowIndex As Integer=8
Do While i <= 5
For j As Integer = 1 To parts.Columns.Count
sheet.Range(i, j).Value2 = parts.Rows(datarowIndex)(j - 1)
Next j
datarowIndex += 1
i += 1
Loop
For j As Integer = 1 To parts.Columns.Count
sheet.Range(7, j).Value2 = parts.Rows(6)(j - 1)
Next j
Step 4. Save the project and Read the Datatable.
C# Code:
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("sample.xls");
}
static private DataTable GetData()
{
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= E:\\TestScript\\Data\\database.accdb;Persist Security Info=False;";
string sqlParts = "select * from parts";
DataTable parts = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connString))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
adapter.Fill(parts);
}
return parts;
}
C# Code:
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start("sample.xls");
}
static private DataTable GetData()
{
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= E:\\TestScript\\Data\\database.accdb;Persist Security Info=False;";
string sqlParts = "select * from parts";
DataTable parts = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connString))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
adapter.Fill(parts);
}
return parts;
}
VB.NET Code:
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
Private Shared Function GetData() As DataTable
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\..\Data\database.accdb;Persist Security Info=False;"
Dim sqlParts As String = "select * from parts"
Dim parts As New DataTable()
Using conn As New OleDbConnection(connString)
Dim adapter As New OleDbDataAdapter(sqlParts, conn)
adapter.Fill(parts)
End Using
Return parts
End Function
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
Private Shared Function GetData() As DataTable
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\..\Data\database.accdb;Persist Security Info=False;"
Dim sqlParts As String = "select * from parts"
Dim parts As New DataTable()
Using conn As New OleDbConnection(connString)
Dim adapter As New OleDbDataAdapter(sqlParts, conn)
adapter.Fill(parts)
End Using
Return parts
End Function